from openpyxl import load_workbook
import glob
import os
import pandas as pd
from sqlalchemy import create_engine

DB_STRING = 'mysql://ccd:123456@127.0.0.1/ccd_datavisual?charset=utf8'
engine = create_engine(DB_STRING)

# cur.execute(sql)
def read_excel():       # 3 型号筛选器
    # print(file)
    workbook_new = pd.ExcelFile(file)
    for i in workbook_new.sheet_names:
        d = pd.read_excel(file,sheet_name=i)
        d.columns = [ i for i in range(len(d.columns))]
        x = d[d[0].str.contains('Product', na=False)]
        # d = d.drop(labels=[0,1,2,3],axis=0)
        d = d[int(x.index.values) + 1:]
        d.columns = [str(num2).strip().replace(" ","_") for num1 in x.values for num2 in num1]
        pd.io.sql.to_sql(d,'ops3_manu2',con=engine,index=False,if_exists="append")
# 封装 encapsulation
# 测试 test
# 生产 production
def read_excel_1():     # 2 1 型号筛选器
    # print(file)
    workbook_new = pd.ExcelFile(file)
    for i in workbook_new.sheet_names:
        d = pd.read_excel(file, sheet_name=i)
        x = d[d['序号'].isnull()].index.tolist()[0]
        d = d[:x]
        # print(d)
        # d.columns = [i for i in range(len(d.columns))]
        # x = d[d[0].str.contains('序号', na=False)]
        # print(x.values)
        # d.columns = [num2 for num1 in x.values for num2 in num1]
        pd.io.sql.to_sql(d, 'ops2_manu1', con=engine, index=False, if_exists="append")

def read_excel_2(name=None):
    workbook_new = pd.ExcelFile(file)
    for i in workbook_new.sheet_names:
        d = pd.read_excel(file, sheet_name=i)
        pd.io.sql.to_sql(d, name, con=engine, index=False, if_exists="append")

def read_excel_3(name=None):
    workbook_new = pd.ExcelFile(file)
    for i in workbook_new.sheet_names:
        d = pd.read_excel(file, sheet_name=i)
        d.columns = [str(x).strip().replace("/", "_") for x in d.columns]
        d = d.fillna(method='ffill')
        pd.io.sql.to_sql(d, name, con=engine, index=False, if_exists="append")

def read_excel_4(name=None):                           # 多 sheet 分表
    workbook_new = pd.ExcelFile(file)
    # 构建字典
    dict_number = {"CP WIP":1,"CP出货信息":2,"FT WIP":3,"FT 出货信息":4}
    for i in workbook_new.sheet_names:
        if i in dict_number :
            if dict_number[i] == 1:
                print(i)
                d = pd.read_excel(file, sheet_name=i,header=2)
                d = d.drop('Commitment Date ',axis=1)
                pd.io.sql.to_sql(d, i.strip().replace(" ",""), con=engine, index=False, if_exists="append")
            elif dict_number[i] == 3:
                print(i)
                d = pd.read_excel(file, sheet_name=i, header=2)
                pd.io.sql.to_sql(d, i.strip().replace(" ",""), con=engine, index=False, if_exists="append")
            elif dict_number[i] == 4:
                print(i)
                d = pd.read_excel(file, sheet_name=i,usecols ="A:I")
                d = d.fillna(method='ffill')
                pd.io.sql.to_sql(d, i.strip().replace(" ",""), con=engine, index=False, if_exists="append")
            else:
                print(i)
                d = pd.read_excel(file, sheet_name=i)
                d = d[['快运单号']].fillna(method='ffill')
                pd.io.sql.to_sql(d, i.strip().replace(" ",""), con=engine, index=False, if_exists="append")


def read_excel_5(name=None,name1=None):                # pandas 差值计算
    workbook_new = pd.ExcelFile(file)
    number = 0
    df1 = df2 = None
    for i in workbook_new.sheet_names:
        df_number = pd.read_excel(file, sheet_name=i,header=2,usecols ="B:N")
        if number == 0:
            df1 = df_number
        elif number % 2 == 0:
            pd.io.sql.to_sql(df1, name, con=engine, index=False, if_exists="append")
            df1 = df_number
            df1 = df1.append(df2)
            write = df1.drop_duplicates(subset=['PO No','Lot No'],keep=False)
            pd.io.sql.to_sql(df1, name1, con=engine, index=False, if_exists="append")
        else :
            if not df2 is None:
                pd.io.sql.to_sql(df2, name, con=engine, index=False, if_exists="append")
            df2 = df_number
            df2 = df2.append(df1)
            write = df2.drop_duplicates(subset=['PO No','Lot No'],keep=False)
            pd.io.sql.to_sql(df2, name1, con=engine, index=False, if_exists="append")
        number += 1

goal_mkdir = "d:/ccd_test/ccd"

for file in glob.glob('{}/**/*'.format(goal_mkdir),recursive=True):
    if os.path.isfile(file):
        # 提取名字分类
        tab_name = os.path.basename(file).split(" ")
        # print("*********************")
        # print(tab_name)
        # print("*********************")
        # print(tab_name)
        # 开始处理
        if tab_name[0] == '111':
            #  封装
            # print("11")
            if tab_name[1] == "111":            # 需要多添加一列用文件 sheet 表做标识列
                read_excel_5(name='ops1_manu1',name1='difference')
            if tab_name[1] == "222":
                print("进入 222")
                read_excel_4()
        if tab_name[0] == '222':
            if tab_name[1] == '111':
                read_excel_1()
            elif tab_name[1] == '222' :
                read_excel_2(name='ops2_manu2')
            elif tab_name[1] == '333':
                read_excel_2(name='ops2_manu3')
            elif tab_name[1] == '444':
                read_excel_3(name='ops2_manu4')
            else:
                tab_list = []
                name = ""
                tab_list = tab_name[1].split("_")
                for x in range(0,len(tab_list)-1):
                    name += str(tab_list[x])
                    name += '_'
                # print("=================")
                # print(tab_list,tab_name)
                # print("=================")
                read_excel_2(name=name.lower())
            # 测试
            pass
        if tab_name[0] == '333':
            # 生产

            read_excel()




